package promis.db.connection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import org.apache.log4j.Logger;
import promis.utility.CanlendarDateFormat;
import promis.utility.CommonService;
import promis.utility.Utilitys;

public class TablesLogsServices {
	
	private static final Logger log = Logger.getLogger(TablesLogsServices.class);
	private boolean IsTable(String TABLE_NAME) throws Exception {
		Connection conn = new dbHelper().getConn();
		Statement stmt = conn.createStatement();				
		try{
			String sql = " select * from SYS.USER_TABLES Where TABLE_NAME  = " + Utilitys.strSql(TABLE_NAME);
			ResultSet rs = stmt.executeQuery(sql);
			
			if(rs != null){
				while(rs.next()){
					if(rs.getString("TABLE_NAME").toUpperCase().equals(CommonService.IsNullRString(TABLE_NAME).toUpperCase())){
						return true;
					}	
				}				
			}
			
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
		}finally{
			stmt.close();
            conn.close();
		}
		return false;
	}
	
	private void CreateTableMonthLogs() throws Exception {
		try
		{
			Date date = Calendar.getInstance().getTime();
			String dateTable = (Integer.parseInt(CommonService.ConvertDateToSAPStringFormat(date, CanlendarDateFormat.DATE_FORMAT_FULLDATE_ONLY_SAP_YEAR_MONTH))-1)+"";
			
			if(IsTable("LOGS_INBOUND_"+ dateTable +"") == false && IsTable("LOGS_OUTBOUND_"+ dateTable +"") == false)
			{
				Connection conn = new dbHelper().getConn();
				Statement stmt = conn.createStatement();
				StringBuilder _strQuery = new StringBuilder();
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE TABLE SYNCHRO.LOGS_INBOUND_"+ dateTable +" ");
				_strQuery.append(" ( ");
				_strQuery.append(" LOGS_ID    NUMBER                             NOT NULL, ");
				_strQuery.append(" USER_ID    VARCHAR2(500 BYTE)                 NOT NULL, ");
				_strQuery.append(" DATED      DATE                               NOT NULL, ");
				_strQuery.append(" LOGGER     VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" \"LEVEL\"    VARCHAR2(500 BYTE)               NOT NULL, ");
				_strQuery.append(" MESSAGE    VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" CREATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" CREATEDON  DATE                               DEFAULT current_date, ");
				_strQuery.append(" UPDATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" UPDATEDON  DATE                               DEFAULT current_date ");
				_strQuery.append(" ) ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTUSED    0 ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   1 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" LOGGING  ");
				_strQuery.append(" NOCOMPRESS  ");
				_strQuery.append(" NOCACHE ");
				_strQuery.append(" NOPARALLEL ");
				_strQuery.append(" MONITORING ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE UNIQUE INDEX SYNCHRO.LOGSINB"+ dateTable +" _"+ dateTable +" _PK ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGSINB"+ dateTable +" _LOGGER_INDX ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGGER) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGSINB"+ dateTable +" _MESSAGE_INDX ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ");
				_strQuery.append(" (MESSAGE) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" ALTER TABLE SYNCHRO.LOGS_INBOUND_"+ dateTable +"  ADD ( ");
				_strQuery.append(" CONSTRAINT LOGS_INBOUND_"+ dateTable +" _PK ");
				_strQuery.append(" PRIMARY KEY ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" USING INDEX SYNCHRO.LOGSINB"+ dateTable +" _"+ dateTable +" _PK) ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" GRANT DELETE, INSERT, SELECT, UPDATE ON SYNCHRO.LOGS_INBOUND_"+ dateTable +"  TO ALL_SPL_OBJECT ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE TABLE SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" ( ");
				_strQuery.append(" LOGS_ID    NUMBER                             NOT NULL, ");
				_strQuery.append(" USER_ID    VARCHAR2(500 BYTE)                 NOT NULL, ");
				_strQuery.append(" DATED      DATE                               NOT NULL, ");
				_strQuery.append(" LOGGER     VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" \"LEVEL\"    VARCHAR2(500 BYTE)               NOT NULL, ");
				_strQuery.append(" MESSAGE    VARCHAR2(3700 BYTE)                NOT NULL, ");
				_strQuery.append(" CREATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" CREATEDON  DATE                               DEFAULT current_date, ");
				_strQuery.append(" UPDATEDBY  VARCHAR2(500 BYTE), ");
				_strQuery.append(" UPDATEDON  DATE                               DEFAULT current_date ");
				_strQuery.append(" ) ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTUSED    0 ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   1 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" LOGGING  ");
				_strQuery.append(" NOCOMPRESS "); 
				_strQuery.append(" NOCACHE ");
				_strQuery.append(" NOPARALLEL ");
				_strQuery.append(" MONITORING ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" MSGINDX ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (MESSAGE) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _INDX ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGGER) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());

				_strQuery = new StringBuilder();
				_strQuery.append(" CREATE UNIQUE INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _PK ON SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" LOGGING ");
				_strQuery.append(" TABLESPACE USERS ");
				_strQuery.append(" PCTFREE    10 ");
				_strQuery.append(" INITRANS   2 ");
				_strQuery.append(" MAXTRANS   255 ");
				_strQuery.append(" STORAGE    ( ");
				_strQuery.append(" INITIAL          64K ");
				_strQuery.append(" NEXT             1M ");
				_strQuery.append(" MINEXTENTS       1 ");
				_strQuery.append(" MAXEXTENTS       UNLIMITED ");
				_strQuery.append(" PCTINCREASE      0 ");
				_strQuery.append(" BUFFER_POOL      DEFAULT ");
				_strQuery.append(" ) ");
				_strQuery.append(" NOPARALLEL ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
				_strQuery = new StringBuilder();
				_strQuery.append(" ALTER TABLE SYNCHRO.LOGS_OUTBOUND_"+ dateTable +"  ADD ( ");
				_strQuery.append(" CONSTRAINT LOGS_OUTBOUND_"+ dateTable +" _PK ");
				_strQuery.append(" PRIMARY KEY ");
				_strQuery.append(" (LOGS_ID) ");
				_strQuery.append(" USING INDEX SYNCHRO.LOGS_OUTBOUND_"+ dateTable +" _PK) ");
				System.out.println(_strQuery.toString());
				stmt.addBatch(_strQuery.toString());
				
	            // Executing executeBatch
	            stmt.executeBatch();
	            stmt.close();
	            conn.close();	
			}else{
				System.out.println("Else Have Tabls");
			}
		}catch(SQLException e){
			e.printStackTrace();
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}
	}
	
	private void MoveMonthLogs() throws Exception {
		try
		{
			Date date = Calendar.getInstance().getTime();
			String dateTable = (Integer.parseInt(CommonService.ConvertDateToSAPStringFormat(date, CanlendarDateFormat.DATE_FORMAT_FULLDATE_ONLY_SAP_YEAR_MONTH))-1)+"";
			
			Connection conn = new dbHelper().getConn();
			Statement stmt = conn.createStatement();
			
			StringBuilder _strQuery = new StringBuilder();
			_strQuery.append(" insert into LOGS_OUTBOUND_"+ dateTable +" value select * from LOGS_OUTBOUND where to_char(DATED,'YYYYMMDD') <=  TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());			
			 
			_strQuery = new StringBuilder();
			_strQuery.append(" delete from LOGS_OUTBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());
			
			_strQuery = new StringBuilder();
			_strQuery.append(" insert into LOGS_INBOUND_"+ dateTable +" value select * from LOGS_INBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());
			
			_strQuery = new StringBuilder();
			_strQuery.append(" delete from LOGS_INBOUND where to_char(DATED,'YYYYMMDD') <= TO_CHAR(CURRENT_DATE -1,'YYYYMMDD') ");
			System.out.println(_strQuery.toString());
			stmt.addBatch(_strQuery.toString());			
			
            // Executing executeBatch
            stmt.executeBatch();
            stmt.close();
            conn.close();
            
		}catch(SQLException e){
			e.printStackTrace();
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getMessage());
			System.out.println(e.getMessage());
		}
	}
	
	public void run(){
		try {
			
			new TablesLogsServices().CreateTableMonthLogs();
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.getMessage());
		} finally{
			try {
				new TablesLogsServices().MoveMonthLogs();
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println(e.getMessage());
			}
		}
	}
}
